package com.huatai.exchange.load;

import com.faneui.common.exception.BusinessException;
import com.faneui.common.utils.ListUtils;
import com.faneui.dal.DBUtils;
import com.faneui.excel.utils.ExcelUtils;
import com.github.promeg.pinyinhelper.Pinyin;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.huatai.exchange.dal.model.ExcFileLoadInfo;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.Map;

@Service("dataExcelFileLoadService")
public class DataExcelFileLoadServiceImpl extends AbstractDataFileLoadService {

    private Map<String, String> sqlMap = Maps.newConcurrentMap();

    @Override
    void loadDataInDataBase(ExcFileLoadInfo loadInfo, List<String> fileNames, String filePath, Map<String, Object> resvMap) throws BusinessException {
        String occurDate = (String) resvMap.get("YYYYMMDD");
        // 数据库数据备份
        String tableName = loadInfo.getTableName();

        String deleteSql = sqlMap.get(tableName + "_delete");
        String insertSql = sqlMap.get(tableName + "_insert");
        List<Object[]> dataList = Lists.newArrayList();
        for (String fileName : fileNames) {

            // 只计算某个excel中的东西
            List<Object[]> objects = null;
            Object o = resvMap.get("EXCEL_START_ROW");
            if (o != null) {
                Integer startRow = Integer.valueOf(o + "");
                Integer startColumn = resvMap.get("EXCEL_START_COLUMN") == null ? 0 : Integer.valueOf(resvMap.get("EXCEL_START_COLUMN") + "");
                objects = ExcelUtils.readPieceExcelXlsx(filePath + fileName, 0, startRow, startColumn);
            } else {
                objects = ExcelUtils.readExcel(filePath + fileName);
            }

            if (StringUtils.isBlank(deleteSql) || StringUtils.isBlank(insertSql)) {
                StringBuilder columnSql = new StringBuilder();
                StringBuilder valueSql = new StringBuilder();
                for (Object object : objects.get(0)) {
                    char[] zhongwen = object.toString().toCharArray();
                    StringBuffer sb = new StringBuffer();
                    for (char c : zhongwen) {
                        sb.append(Pinyin.toPinyin(c));
                    }
                    String yingwen = sb.toString();
                    yingwen = yingwen.equalsIgnoreCase("BY") ? "BYBY" : yingwen;
                    if (yingwen.length() > 30)
                        yingwen = yingwen.substring(0, 30);
                    if ((columnSql + ",").contains("," + yingwen + ",")) {
                        for (int i = 1; ; i++) {
                            String tmp = yingwen + i;
                            if (!(columnSql + ",").contains("," + tmp + ",")) {
                                yingwen = tmp;
                                break;
                            }
                        }
                    }
                    columnSql.append(",").append(yingwen);
                    valueSql.append(",?");
                }
                insertSql = "insert into " + tableName + "(occur_date" + columnSql.toString() + ")values(?" + valueSql.toString() + ")";
                deleteSql = "delete " + tableName + " where occur_date = ? ";
                sqlMap.put(tableName + "_insert", insertSql);
                sqlMap.put(tableName + "_delete", deleteSql);
            }

            if (objects.size() != 1) {
                for (int i = 1; i < objects.size(); i++) {
                    List<Object> oList = Lists.newArrayList();
                    oList.add(occurDate);
                    oList.addAll(Lists.newArrayList((Object[]) (objects.get(i))));
                    dataList.add(oList.toArray());
                }
            }
        }
        DBUtils.execute(localConnection.getConn(), deleteSql, occurDate);
        if (ListUtils.isNotEmpty(dataList))
            DBUtils.insertBatch(localConnection.getConn(), insertSql, dataList);
    }


}
